package com.cola.dao;

import com.cola.entity.export.ExportGoodsPrice;
import com.cola.utils.BasicRowProcessorFix;
import com.cola.utils.DBControl;
import com.cola.utils.DateUtils;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 物料价格管理Dao
 * @author Max
 * @since 2017/9/8
 */
public class GoodsPriceDao extends BaseDao {
    private static volatile GoodsPriceDao instance = null;
    private static final Logger logger = LoggerFactory.getLogger(GoodsPriceDao.class);

    public static GoodsPriceDao getInstance() {
        if (instance == null) {
            synchronized (GoodsPriceDao.class) {
                if (instance == null) {
                    instance = new GoodsPriceDao();
                }
            }
        }
        return instance;
    }

    /**
     * 搜索商品价格列表
     * @param offset 起始位置
     * @param limit 页面大小
     * @param filter 搜索条件
     * @return List<Map<String,Object>>
     * @author Max
     * @since 2017/9/11
     */
    public List<Map<String,Object>> getGoodsPriceList(Integer offset,Integer limit,String filter){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT a.*,b.area_name as area_name,c.area_name as city_name ,d.`name` as channel_name FROM `cola_goods_config` a " +
                    "INNER JOIN system_area b on a.sale_area_no = b.area_no " +
                    "INNER JOIN system_area c on a.sale_city_no = c.area_no " +
                    "INNER JOIN system_dictionary d on a.sale_channel_no = d.d_no where a.status=1 "+filter+" limit ?,?";

            return getQueryRunner().query(conn,sql,new MapListHandler(new BasicRowProcessorFix()),offset,limit);
        }catch (Exception e){
            logger.error("搜索商品价格列表异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }

    /**
     * 搜索商品价格数量
     * @param filter 搜索条件
     * @return Integer
     * @author Max
     * @since 2017/9/11
     */
    public Integer getGoodsPriceListCount(String filter){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT count(a.config_id) FROM `cola_goods_config` a where a.status=1 "+filter;
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                return rs.getInt(1);
            }
        }catch (Exception e){
            logger.error("搜索商品价格数量！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return -1;
    }

    /**
     * 根据物料id获取价格配置信息
     * @param configId 配置Id
     * @return List<Map<String,Object>>
     * @author Max
     * @since 2017/9/11
     */
    public Map<String,Object> getGoodsPriceById(Integer configId){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT a.*,b.area_name as area_name,c.area_name as city_name ,d.`name` as channel_name FROM `cola_goods_config` a " +
                    "INNER JOIN system_area b on a.sale_area_no = b.area_no " +
                    "INNER JOIN system_area c on a.sale_city_no = c.area_no " +
                    "INNER JOIN system_dictionary d on a.sale_channel_no = d.d_no where a.status=1 and a.config_id =?";

            return getQueryRunner().query(conn,sql,new MapHandler(new BasicRowProcessorFix()),configId);
        }catch (Exception e){
            logger.error("搜索商品价格列表异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }


    /**
     * 获取已经存在的价格
     * @return Map<String,Map<String,Object>>
     * @author Max
     * @since 2017/9/8
     */
    public Map<String,String> getExistsGoodsPrice(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Map<String,String> existsGoodsPriceMap = new HashMap<String,String>();
        String tempKey = null;
        try{
            conn = DBControl.getConnection();
            String sql = "SELECT goods_no,sale_area_no,sale_channel_no ,MAX(end_time) as end_time FROM `cola_goods_config` " +
                    " GROUP BY goods_no,sale_area_no,sale_channel_no;";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                tempKey = rs.getString("goods_no")+rs.getString("sale_area_no")
                        +rs.getString("sale_channel_no");
                existsGoodsPriceMap.put(tempKey,rs.getString("end_time"));
            }
            return existsGoodsPriceMap;
        }catch (Exception e){
            logger.error("获取已经存在的价格",e);
        }finally {
            DbUtils.closeQuietly(conn,ps,rs);
        }
        return null;
    }


    /**
     * 插入商品价格
     * @param List<List<String>> list 导入列表
     * @return true 成功 false 失败
     * @author Max
     * @since 2017/9/11
     */
    public boolean insertGoodsPrice(List<List<String>> list){
        Connection conn = null;
        Map<String,String> areaToCityMap = CommonDao.getInstance().getAreaToCity();
        try{
            conn = DBControl.getConnection();
            String sql = "insert into cola_goods_config(goods_no,goods_price,sale_city_no,sale_area_no,sale_channel_no,start_time,end_time" +
                    ",check_status,create_time) values(?,?,?,?,?,?,?,?,?)";
            Object[][] param= new Object[list.size()][9];
            for (int i =0;i< list.size();i++){
                param[i][0]=list.get(i).get(0);
                param[i][1]=list.get(i).get(3);
                param[i][2]=areaToCityMap.get(list.get(i).get(1));
                param[i][3]=list.get(i).get(1);
                param[i][4]=list.get(i).get(2);
                param[i][5]=list.get(i).get(4);
                param[i][6]=list.get(i).get(5);
                param[i][7]=1;
                param[i][8]= DateUtils.getCurrentDateTime();
            }
            getQueryRunner().batch(conn,sql,param);
            return true;
        }catch (Exception e){
            logger.error("插入商品价格异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }


    /**
     * 保存商品价格信息修改
     * @param configId
     * @param goodsPrice
     * @return true 成功 false 失败
     * @author Max
     * @since 2017/9/11
     */
    public boolean saveGoodsPriceConfig(Integer configId, BigDecimal goodsPrice){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "update cola_goods_config set goods_price =? where config_id =?";

            return getQueryRunner().update(conn,sql,goodsPrice,configId)>0;
        }catch (Exception e){
            logger.error("保存商品价格信息修改异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 停用价格配置
     * @param configId 价格配置Id
     * @return true 成功 false 失败
     * @author Max
     * @since 2017/9/11
     */
    public boolean doGoodsPriceOff(Integer configId){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "update cola_goods_config set check_status =-1 where config_id =?";
            return getQueryRunner().update(conn,sql,configId)>0;
        }catch (Exception e){
            logger.error("停用价格配置异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return false;
    }

    /**
     * 获取价格导出数据
     * @return List<ExportGoodsPrice>
     * @author Max
     * @since 2017/9/11
     */
    public List<ExportGoodsPrice> getExportGoodsPriceInfo(){
        Connection conn = null;
        try{
            conn = DBControl.getConnection();
            String sql = "select * from cola_goods_config where status =1";

            return getQueryRunner().query(conn,sql,new BeanListHandler<ExportGoodsPrice>(ExportGoodsPrice.class));

        }catch (Exception e){
            logger.error("获取价格导出数据异常！",e);
        }finally {
            DbUtils.closeQuietly(conn);
        }
        return null;
    }


}
